/** 
Copyright (c) 2010, Sheikh Abdul Wahid Ahmed
Details @ http://extremecodeworld.codeplex.com/license
**/

using System;
using System.Data;
using System.Data.Common;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Collections;
namespace EXtremecode.Utils
{
	/// <summary>
	/// Summary description for DataAccessorUtil.
	/// </summary>
	public class DBUtil
	{
		public static void CopyDataRow(DataRow source,DataRow target)
        {
            foreach (DataColumn dc in source.Table.Columns)
            {
                if(target.Table.Columns.Contains(dc.ColumnName))
                {
                    target[dc.ColumnName] = source[dc.ColumnName];
                }
            }
        }
        public static DataTable GetGroupedTable(DataView dv, string groupBy,params string[] nameExpressionPairs)
		{
			string[] groupByColumns = groupBy.Split(',');
			DataTable dt = dv.Table;
			DataView dvExcluded = new DataView(dt);//for keeping seperate, excluded from filtered rows.
			string userAppliedFilter = string.Empty;

			//---create new grouped dataTable.
			DataTable dtGrouped=new DataTable();
            
			//add groupby colums
			for(int i=0; i<groupByColumns.Length; i++)
			{
				DataColumn dc=new DataColumn();
				dc.ColumnName=dt.Columns[groupByColumns[i]].ColumnName;
				dc.DataType=dt.Columns[groupByColumns[i]].DataType;
				dc.Expression=dt.Columns[groupByColumns[i]].Expression;

				dtGrouped.Columns.Add(dc);
			}
            
			//add expression columns
			for(int i=0; i<nameExpressionPairs.Length; i++)
			{
				string[] nameExpressionPair = nameExpressionPairs[i].Split(',');
				if(nameExpressionPair.Length != 2 || nameExpressionPair[0].Trim() == string.Empty) 
				{
					throw new Exception(string.Format("UC Name expression pair is not in proper format: '{0}'"
						,nameExpressionPairs[i])); 
				}

				DataColumn dc=new DataColumn();
				dc.ColumnName=nameExpressionPair[0];
				dc.DataType=typeof(decimal);
				dtGrouped.Columns.Add(dc);
			}
            

			//--now add aggregated rows into grouped table. 
            
			//Add user applied filter on dv.
			userAppliedFilter = dvExcluded.RowFilter = (dv.RowFilter == null || dv.RowFilter == string.Empty) ?
				" 1=1 " : dv.RowFilter;
            


			while (dvExcluded.Count > 0)
			{
				DataRow drNew = dtGrouped.NewRow();
				//get firstRow from excluded dv and make filter and populate newRow Simultaneously
				string filter = string.Empty;
				string notFilter = string.Empty;
				DataRowView drv=dvExcluded[0];
				string strAnd = string.Empty;
				string strOr = string.Empty;
                
				foreach (string columnName in groupByColumns)
				{
					string singleQote= (dtGrouped.Columns[columnName].DataType == typeof(DateTime) || 
						dtGrouped.Columns[columnName].DataType == typeof(string))?
						"'": string.Empty;

					if (drv[columnName] == DBNull.Value)
					{
						throw new Exception(
							string.Format("UC the value of column '{0}' should not be null", columnName)); 
					}
					drNew[columnName] = drv[columnName];
					filter += strAnd + columnName + " = " + singleQote + drv[columnName].ToString() + singleQote;
					notFilter += strAnd + columnName + " <> " + singleQote + drv[columnName].ToString() + singleQote;
					strAnd = " and ";
				}

				//calculated aggrigate fields.
				foreach (string pair in nameExpressionPairs)
				{
					string[] nameExpressionPair = pair.Split(',');

					string columnName = nameExpressionPair[0];
					string expression = nameExpressionPair[1];

					drNew[columnName] = dt.Compute(expression, userAppliedFilter + " And " + filter); 
				}

				//add newRow to the grouped table.
				dtGrouped.Rows.Add(drNew);

				//append new filter into dvExcluded
				dvExcluded.RowFilter +=  " and " + notFilter;
                
			}
			return dtGrouped;
            
		}
	}
}
